package com.seari.custody.util;

import cn.hutool.poi.excel.cell.CellUtil;
import com.google.common.base.Strings;
import com.seari.custody.pojo.CellRangeAddressExcelVo;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;

import java.util.LinkedList;
import java.util.List;

/**
 * @Author Zhongks
 * @Description //TODO POI导出excel工具类
 * @Date 17:16 2021/5/11
 * @Param
 * @return
 **/


public class POIUtil {
    /**
     * @return void
     * @Author Zhongks
     * @Description //TODO 拷贝sheet（表）
     * @Date 17:16 2021/5/11
     * @Param [targetSheet, sourceSheet, targetWork, sourceWork, startRow, cellRangeAddressExcelVoList]
     **/
    public static void copySheet(Sheet targetSheet, Sheet sourceSheet,
                                 Workbook targetWork, Workbook sourceWork, int startRow,
                                 List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList) {
        if (targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        //设置单元格默认宽度
        targetSheet.setDefaultColumnWidth(25);
        //复制源表中的行
        for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
            Row sourceRow = sourceSheet.getRow(i);
            Row targetRow = targetSheet.createRow(i + startRow);  //创建新的row
            if (sourceRow != null) {
                copyRow(targetRow, sourceRow,
                        targetWork, sourceWork);
            }
        }

        //自定义合并单元格样式（若不需要进行单元格合并操作，将cellRangeAddressExcelVoList赋值为null即可）
        if (!CollectionUtils.isEmpty(cellRangeAddressExcelVoList)) {
            //合并单元格
            for (CellRangeAddressExcelVo model : cellRangeAddressExcelVoList) {
                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(model.getFirstRow(), model.getLastRow(), model.getFirstCol(), model.getLastCol()));
            }
        }
    }

    public static List<CellRangeAddressExcelVo> getCellRangeAddressExcelVoList(int row, int col) {
        //合并单元格坐标位置
        List<CellRangeAddressExcelVo> list = new LinkedList<>();
        for (int i = 0; i < 15; i++) {
            if (i < 7) {
                CellRangeAddressExcelVo cellRangeAddressExcelVo = new CellRangeAddressExcelVo(row, row + 1, i, i);
                list.add(cellRangeAddressExcelVo);
            } else {
                CellRangeAddressExcelVo cellRangeAddressExcelVo = new CellRangeAddressExcelVo(row, row, i, i + 1);
                list.add(cellRangeAddressExcelVo);
                i++;
            }
        }
        return list;
    }

    /**
     * @return void
     * @Author Zhongks
     * @Description //TODO 拷贝row(行)
     * @Date 17:17 2021/5/11
     * @Param [targetRow, sourceRow, targetWork, sourceWork]
     **/
    public static void copyRow(Row targetRow, Row sourceRow,
                               Workbook targetWork, Workbook sourceWork) {
        if (targetRow == null || sourceRow == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");
        }

        //设置行高
        targetRow.setHeight(sourceRow.getHeight());

        for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            Cell targetCell = null;

            if (sourceCell != null&&!Strings.isNullOrEmpty(String.valueOf(CellUtil.getCellValue(sourceCell)))) {
                if (targetCell == null) {
                    targetCell = targetRow.createCell(i);
                }
                //拷贝单元格，包括内容和样式
                copyCell(targetCell, sourceCell, targetWork, sourceWork);
            }
        }
    }

    /**
     * @return void
     * @Author Zhongks
     * @Description //TODO 拷贝cell（单元格）
     * @Date 17:18 2021/5/11
     * @Param [targetCell, sourceCell, targetWork, sourceWork]
     **/
    public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork) {
        if (targetCell == null || sourceCell == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        CellStyle targetCellStyle = targetWork.createCellStyle();
        targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());//拷贝样式
        //重新添加样式（这里可以根据你的需要重新进行单元格样式添加）
        /*targetCellStyle.setBorderTop(BorderStyle.THIN);//设置上边框线
        targetCellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框线
        targetCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框线
        targetCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框线*/
        targetCell.setCellStyle(targetCellStyle);
        try {
            targetCell.setCellValue(sourceCell.getStringCellValue());
        } catch (Exception e) {
            targetCell.setCellValue(sourceCell.getNumericCellValue());

        }
    }

}